using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Data.SQLite;
using System.Data;

namespace Activa.CoolStorage
{
    public class CSDataProviderSQLite : CSDataProvider
    {
        public CSDataProviderSQLite(string connectionString)
            : base(connectionString)
        {
        }

        public CSDataProviderSQLite(string fileName, bool useDateTimeTicks)
            : base("Data Source=" + fileName + ";DateTimeFormat=" + (useDateTimeTicks ? "Ticks":"ISO8601"))
        {
        }

        protected override IDbConnection CreateConnection()
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);

            conn.Open();

            return conn;
        }

        protected override CSDataProvider Clone()
        {
            return new CSDataProviderSQLite(ConnectionString);
        }

        protected override IDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters)
        {
            SQLiteCommand sqlCommand = (SQLiteCommand) Connection.CreateCommand();

            sqlCommand.Transaction = (SQLiteTransaction)CurrentTransaction;

            if (sqlQuery.ToUpper().StartsWith("DELETE ") || sqlQuery.ToUpper().StartsWith("SELECT ") || sqlQuery.ToUpper().StartsWith("UPDATE ") || sqlQuery.ToUpper().StartsWith("INSERT ") || sqlQuery.ToUpper().StartsWith("CREATE "))
                sqlCommand.CommandType = CommandType.Text;
            else
                sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", "@${name}");

            if (parameters != null && !parameters.IsEmpty)
                foreach (CSParameter parameter in parameters)
                {
                    IDbDataParameter dataParameter = sqlCommand.CreateParameter();

                    dataParameter.ParameterName = "@" + parameter.Name.Substring(1);
                    dataParameter.Direction = ParameterDirection.Input;
                    dataParameter.Value = ConvertParameter(parameter.Value);

                    sqlCommand.Parameters.Add(dataParameter);
                }

            return sqlCommand;
        }

        protected override string QuoteField(string fieldName) { return "\"" + fieldName.Replace(".","\".\"") + "\""; }
        protected override string QuoteTable(string tableName) { return "\"" + tableName + "\""; }

        protected override string NativeFunction(string functionName, ref string[] parameters)
        {
            switch (functionName.ToUpper())
            {
                case "LEN": return "LENGTH";
                case "LEFT": return "SUBSTR(" + parameters[0] + ",1," + parameters[1] + ")";
                default: return functionName.ToUpper();
            }
        }

        protected override string BuildSelectSQL(string tableName, string tableAlias, string[] columnList, string[] columnAliasList, string[] joinList, string whereClause, string orderBy, int startRow, int maxRows, bool quoteColumns, bool unOrdered)
        {
            string sql = "select";

            if (quoteColumns)
                columnList = QuoteFieldList(columnList);

            string[] columnNames = new string[columnList.Length];

            for (int i = 0; i < columnList.Length; i++)
            {
                columnNames[i] = columnList[i];

                if (columnAliasList != null)
                    columnNames[i] += " " + columnAliasList[i];
            }

            sql += " " + String.Join(",", columnNames);

            sql += " from " + QuoteTable(tableName) + " " + tableAlias;

            if (joinList != null && joinList.Length > 0)
                foreach (string joinExpression in joinList)
                    sql += " " + joinExpression;

            if (!string.IsNullOrEmpty(whereClause))
                sql += " where " + whereClause;

            if (!string.IsNullOrEmpty(orderBy))
                sql += " order by " + orderBy;

            if (maxRows > 0)
                sql += " limit " + maxRows;

            if (startRow > 1)
                sql += " offset " + (startRow - 1);

            return sql;

        }

        protected override string BuildInsertSQL(string tableName, string[] columnList, string[] valueList, string[] primaryKeys, string identityField)
        {
            string sql;

            if (columnList.Length > 0)
            {
                sql = String.Format("insert into {0} ({1}) values ({2})",
                                    QuoteTable(tableName),
                                    String.Join(",", QuoteFieldList(columnList)),
                                    String.Join(",", valueList)
                                    );
            }
            else
            {
                sql = String.Format("insert into {0} default values", QuoteTable(tableName));
            }

            if (primaryKeys != null && primaryKeys.Length > 0 && identityField != null)
                sql += String.Format(";SELECT {0} from {1} where {2} = last_insert_rowid()", String.Join(",", QuoteFieldList(primaryKeys)), QuoteTable(tableName), identityField);

            return sql;
        }

        protected override bool SupportsNestedTransactions
        {
            get { return false; }
        }

        protected override bool SupportsSequences
        {
            get { return false; }
        }

        protected override bool SupportsMultipleStatements
        {
            get { return true; }
        }

        protected override bool RequiresSeperateIdentityGet
        {
            get { return false; }
        }
    }
}
